package com.sq.swing.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

public class DerbyTool {

	public static void main(String[] args) {
		try { // load the driver
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			System.out.println("Load the embedded driver");
			Connection conn = null;
			Properties props = new Properties();
			props.put("user", "root");
			props.put("password", "root");

			// create and connect the database named JFViewer
			conn = DriverManager
					.getConnection(
							"jdbc:derby:C:/sq/derby/Viewer;create=true",
							props);
			System.out.println("create and connect to JFViewer");
			conn.setAutoCommit(false);

			// create table
			StringBuffer stbSql = new StringBuffer();
			// stbSql.append("DROP TABLE TJOURNALDATA                                                  ");
			stbSql.append("CREATE TABLE TJOURNALDATA(                                                ");
			stbSql.append("IP_ADDRESS VARCHAR(15),                                                   ");
			stbSql.append("TRADE_NUMBER VARCHAR(10),                                                 ");
			stbSql.append("TRANSACTION_DATETIME CHAR(17),                                            ");
			stbSql.append("SERIAL_NUMBER VARCHAR(3),                                                 ");
			stbSql.append("JOURNAL_DATA clob,                                                        ");
			stbSql.append("TRADE_DATETIME CHAR(17),                                                  ");
			stbSql.append("BRANCH_NUMBER VARCHAR(10) NOT NULL,                                       ");
			stbSql.append("TERMINAL_NUMBER VARCHAR(4) NOT NULL,                                      ");
			stbSql.append("OPERATOR_ID	VARCHAR(16),                                                 ");
			stbSql.append("KEYWORD_1 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_2 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_3 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_4 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_5 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_6 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_7 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_8 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_9 VARCHAR(64),                                                    ");
			stbSql.append("KEYWORD_10 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_11 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_12 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_13 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_14 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_15 VARCHAR(64),                                                   ");
			stbSql.append("KEYWORD_16 VARCHAR(64),                                                   ");
			stbSql.append("DIVISION_KEY CHAR(7) NOT NULL,                                            ");
			stbSql.append("PRIMARY KEY (IP_ADDRESS,TRADE_NUMBER,TRANSACTION_DATETIME,SERIAL_NUMBER)) ");

			Statement statement = conn.createStatement();
			statement.execute(stbSql.toString());

			// statement.execute("insert into tjournaldata (IP_ADDRESS, TRADE_NUMBER, TRANSACTION_DATETIME, SERIAL_NUMBER, TRADE_DATETIME, BRANCH_NUMBER,TERMINAL_NUMBER, DIVISION_KEY) values ('123','123','123','123','123','123','123','123') ");

			statement.close();
			System.out.println("Closed result set and statement");
			conn.commit();
			conn.close();
			System.out.println("Committed transaction and closed connection");

		} catch (Exception e) {
			// handle the exception
			e.printStackTrace();
		}
		System.out.println("SimpleApp finished");
	}

}